﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using DMS.BaseClass;
using DMS.Business.Modules.SupplierManagement;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DMS.Base;
using DMS.Business.BaseClass;

namespace DMS.Business.Utility
{
    public class ProjectHelper : BaseService
    {
        #region SQL查询句子
        private const string SQL_SPM_ProjectInfo_Count = @"SELECT COUNT(*) AS Total
                                                                FROM [dbo].[SPM_Projects]
                                                            @joinClause@";

        private const string SQL_SPM_ProjectInfo = @"select {0} t1.*,t2.ClassName from SPM_Projects t1 
                                                                    inner join MIM_MaterialClass t2
                                                                    on t1.ClassID = t2.ClassID
                                                                    @joinClause@";

        private const string SQL_SPM_ProjectInfo_Order = @"ORDER BY [ProjectCode]";

        private const string SQL_SPM_ProjectInfo_Add = @"INSERT INTO [dbo].[SPM_Projects]
                                                               ([ProjectID]
                                                               ,[ClassID]
                                                               ,[ProjectCode]
                                                               ,[ProjectName]
                                                               ,[Status]
                                                               ,[Creator]
                                                               ,[CreatorID]
                                                               ,[CreateTime]
                                                               ,[OwnerDeptId])
                                                         select @ProjectID
                                                               ,@ClassID
                                                               ,@ProjectCode
                                                               ,@ProjectName
                                                               ,@Status
                                                               ,@Creator
                                                               ,@CreatorID
                                                               ,@CreateTime
                                                               ,dbo.f_GetTypeDeptID(ou.DeptID)
                                                            from org_users ou where ou.userid=@userID";

        private const string SQL_SPM_ProjectInfo_Edit = @"UPDATE [dbo].[SPM_Projects]
                                                           SET [ProjectID] = @ProjectID
                                                              ,[ClassID] = @ClassID
                                                              ,[ProjectCode] = @ProjectCode
                                                              ,[ProjectName] = @ProjectName
                                                              ,[Status] = @Status
                                                              ,[Creator] = @Creator
                                                              ,[CreatorID] = @CreatorID
                                                              ,[CreateTime] = @CreateTime
                                                         WHERE [ProjectID] = @ProjectID";

        private const string SQL_SPM_ProjectInfo_Del = @"DELETE FROM [dbo].[SPM_Projects]
                                                          WHERE [ProjectID] = @ProjectID";
        #endregion

        public static ProjectInfo GetProjectByID(string pID)
        {
            int total = 0;
            string condition = string.Format("AND [ProjectID] = '{0}'", pID.ToString().ToUpper());
            List<ProjectInfo> list = GetProjectInfos(1, 0, out total, condition, true);

            ProjectInfo info = null;
            if (list.Count > 0)
            {
                info = list[0];
            }

            return info;
        }

        public static List<ProjectInfo> GetAllProjects()
        {
            int total =0;
            return GetProjectInfos(0, 0, out total, "", false);
        }

        /// <summary>
        /// 获取项目，包含郊市
        /// </summary>
        /// <returns></returns>
        public static List<ProjectInfo> GetAllProjectsIncludeOutskirts()
        {
            int total = 0;
            return GetProjectInfos(0, 0, out total, "", true);
        }

        /// <summary>
        /// 获取项目信息列表
        /// </summary>
        /// <param name="limite">返回数量限制，-1为不限制</param>
        /// <param name="page">页码</param>
        /// <param name="total">总记录数</param>
        /// <param name="condition">查询条件</param>
        /// <param name="includeOutskirts">是否包含郊市</param>
        /// <returns></returns>
        public static List<ProjectInfo> GetProjectInfos(int limite, int page, out int total, string condition, bool includeOutskirts)
        {
            total = 0;
            Database db = DBHelper.Instance;
            string topClause = string.Empty;

            string joinClause = string.Empty;
            string andClause = string.Empty;
            string userid = SystemContext.Current.LoginUser == null ? "" : SystemContext.Current.LoginUser.ID.ToString();
            if (!string.IsNullOrEmpty(userid) && !includeOutskirts)
            {
                joinClause = "join org_users ou on OwnerDeptID = dbo.f_GetTypeDeptID(ou.deptid)";
                andClause = " and ou.userid='" + userid + "' ";
            }

            string whereClause = string.Format(" where 1 = 1 {0}", condition);

            string countSql = string.Format("{0} {1} @andClause@", SQL_SPM_ProjectInfo_Count, whereClause);

            countSql = countSql.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);

            if (limite > 0)
            {
                topClause = string.Format("Top {0}", limite.ToString()); 

                if (page > 1)
                {
                    int pageCount = (page - 1) * limite;
                    string subClause =
                        string.Format(
                            "SELECT TOP {0} [ProjectID] FROM [dbo].[SPM_Projects] sp @joinClause@ {1} @andClause@ {2}",
                            pageCount.ToString(), whereClause, SQL_SPM_ProjectInfo_Order);
                    subClause = subClause.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);
                    whereClause += string.Format("AND [ProjectID] NOT IN({0})", subClause);
                }
            }
            string mainClause = string.Format(SQL_SPM_ProjectInfo, topClause);
            string sql = string.Format("{0} {1} @andClause@ {2}", mainClause, whereClause,SQL_SPM_ProjectInfo_Order);

            sql = sql.Replace("@joinClause@", joinClause).Replace("@andClause@", andClause);

            DbCommand command = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(command);
            List<ProjectInfo> list = new List<ProjectInfo>();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                total = GetRecordCount(countSql, null);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    ProjectInfo info = new ProjectInfo(dr["ProjectID"].ToString());
                    FillProjectInfoContent(dr, info);

                    list.Add(info);
                }
            }

            return list;
        }

        public static void AddProjectInfo(ProjectInfo info)
        {
            ProjectInfo info2 = GetProjectByID(info.ProjectID);
            if (info2 != null)
            {
                UpdateProjectInfo(info);
                return;
            }

            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_ProjectInfo_Add);
            FillProjectInfoParams(cmd, info);

            db.ExecuteNonQuery(cmd);
        }
        public static void UpdateProjectInfo(ProjectInfo info)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_ProjectInfo_Edit);
            FillProjectInfoParams(cmd, info);

            db.ExecuteNonQuery(cmd);
        }
        public static void DeleteMaterialInfo(ProjectInfo mInfo)
        {
            Database db = DBHelper.Instance;
            DbCommand cmd = db.GetSqlStringCommand(SQL_SPM_ProjectInfo_Del);
            FillProjectInfoParams(cmd, mInfo);

            db.ExecuteNonQuery(cmd);
        }

        /// <summary>
        /// 填充项目信息属性信息
        /// </summary>
        /// <param name="dr">数据行</param>
        /// <param name="info">项目信息对象</param>
        private static void FillProjectInfoContent(DataRow dr, ProjectInfo info)
        {
            // 检查数据行参数
            if (dr == null)
            {
                throw new ArgumentNullException("dr", "填充项目信息内容时，项目信息数据行dr不能为空。");
            }
            if (dr["ProjectID"] == null || dr["ProjectID"].ToString().Trim() == string.Empty)
            {
                throw new ArgumentNullException("ProjectID", "填充项目信息内容时，项目信息数据行中的ProjectID字段为空值，请检查数据。");
            }

            // 检查项目信息对象
            if (info == null)
            {
                info = new ProjectInfo(dr["ProjectID"].ToString());
            }

            // 装载项目信息内容
            try
            {
                info.ProjectName = dr["ProjectName"].ToString();
                info.ProjectCode = dr["ProjectCode"].ToString();
                info.ClassID = dr["ClassID"].ToString();
                info.ClassName = dr["ClassName"].ToString();
                
                if (dr["CreatorID"] != null)
                {
                    info.CreatorID = dr["CreatorID"].ToString();
                }
                if (dr["CreateTime"] != null)
                {
                    info.CreateTime = Convert.ToDateTime(dr["CreateTime"].ToString());
                }

                if (dr["Status"] != null)
                {
                    info.Status = (StatusType)Enum.Parse(typeof(StatusType), dr["Status"].ToString());
                }
            }
            catch (Exception ex)
            {
                string msg = string.Format("项目信息\"{0}\"({1})在装载属性信息时出错", info.ProjectName, info.ProjectID.ToString());
                LogHelper.LogException(ex, msg);

                info = null;
            }
        }

        private static void FillProjectInfoParams(DbCommand cmd, ProjectInfo info)
        {
            DbParameter ProjectID = cmd.CreateParameter();
            ProjectID.ParameterName = "ProjectID";
            ProjectID.Value = info.ProjectID.ToString();
            cmd.Parameters.Add(ProjectID);

            DbParameter ProjectName = cmd.CreateParameter();
            ProjectName.ParameterName = "ProjectName";
            ProjectName.Value = info.ProjectName.ToString();
            cmd.Parameters.Add(ProjectName);

            DbParameter ProjectCode = cmd.CreateParameter();
            ProjectCode.ParameterName = "ProjectCode";
            ProjectCode.Value = info.ProjectCode.ToString();
            cmd.Parameters.Add(ProjectCode);

            DbParameter ClassID = cmd.CreateParameter();
            ClassID.ParameterName = "ClassID";
            ClassID.Value = info.ClassID.ToString();
            //ClassID.Value=new Guid("27A65B89-B6A8-4C0B-B67A-6DE89B90E513");
            cmd.Parameters.Add(ClassID);

            DbParameter Creator = cmd.CreateParameter();
            Creator.ParameterName = "Creator";
            Creator.Value = info.CreatorName.ToString();
            cmd.Parameters.Add(Creator);

            DbParameter CreatorID = cmd.CreateParameter();
            CreatorID.ParameterName = "CreatorID";
            CreatorID.Value = info.CreatorID.ToString();
            cmd.Parameters.Add(CreatorID);

            DbParameter CreateTime = cmd.CreateParameter();
            CreateTime.ParameterName = "CreateTime";
            CreateTime.Value = info.CreateTime;
            cmd.Parameters.Add(CreateTime);

            DbParameter Status = cmd.CreateParameter();
            Status.ParameterName = "Status";
            Status.Value = (int)info.Status;
            cmd.Parameters.Add(Status);

            DbParameter userID = cmd.CreateParameter();
            userID.ParameterName = "userID";
            userID.Value = SystemContext.Current.LoginUser.ID;
            cmd.Parameters.Add(userID);
        }
    }
}
